package me.lxw.blogsystem.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import me.lxw.blogsystem.domain.Article;
import me.lxw.blogsystem.domain.Category;
import me.lxw.blogsystem.domain.Page;
import me.lxw.blogsystem.utils.DBUtils;

/**
 * 文章数据访问对象
 * 
 * @author 梁兴威
 * 
 */
public class ArticleDAO {

    public List<Article> findAll() throws SQLException {
        // 1.获取链接
        Connection conn = DBUtils.getConnection();
        // 2.拼接SQL
        String sql =
            "select a.*, b.title as category_title from article a inner join category b on a.category_id = b.id where a.status = 1 order by a.id desc";
        // 3.获取执行者，发送SQL
        PreparedStatement st = conn.prepareStatement(sql);
        ResultSet rs = st.executeQuery();

        // 4.处理结果
        List<Article> articleList = new ArrayList<Article>();
        while (rs.next()) {
            // id,title,author,category_id,category_title,description,cover_img,content,view,create_time,update_time,status,banner
            Article article = new Article();
            article.setId(rs.getInt("id"));
            article.setTitle(rs.getString("title"));
            article.setAuthor(rs.getString("author"));
            article.setCategoryId(rs.getInt("category_id"));
            article.setCategoryTitle(rs.getString("category_title"));
            article.setDescription(rs.getString("description"));
            article.setCoverImg(rs.getString("cover_img"));
            article.setContent(rs.getString("content"));
            article.setView(rs.getInt("view"));
            article.setCreateTime(rs.getDate("create_time"));
            article.setUpdateTime(rs.getDate("update_time"));
            article.setStatus(rs.getInt("status"));
            article.setBanner(rs.getBoolean("banner"));
            articleList.add(article);
        }

        // 5.关闭资源
        DBUtils.closeResource(conn, st, rs);

        return articleList;
    }

    /**
     * 查询所有数据并进行分页
     * 
     * @param pageNo
     * @return
     * @throws SQLException
     */
    public Page<Article> findAll(int pageNo) throws SQLException {
        // 查询所有的条数
        String countSql =
            "select count(1) from article a inner join category b on a.category_id = b.id where a.status = 1";
        int totalCount = this.countAll(countSql);
        Page<Article> page = new Page<Article>(pageNo);
        page.setTotalCount(totalCount);
        // 获取连接
        Connection conn = DBUtils.getConnection();
        // 分页查询
        String sql =
            "select a.*, b.title as category_title from article a inner join category b on a.category_id = b.id where a.status = 1 order by id desc limit ?, ?";
        PreparedStatement st = conn.prepareStatement(sql);
        st.setInt(1, page.getStart());
        st.setInt(2, page.getPageSize());
        ResultSet rs = st.executeQuery();
        List<Article> articeList = new ArrayList<Article>();
        while (rs.next()) {
            Article article = new Article();
            article.setId(rs.getInt("id"));
            article.setView(rs.getInt("view"));
            article.setAuthor(rs.getString("author"));
            article.setCategoryId(rs.getInt("category_id"));
            article.setContent(rs.getString("content"));
            article.setCoverImg(rs.getString("cover_img"));
            article.setTitle(rs.getString("title"));
            article.setDescription(rs.getString("description"));
            article.setUpdateTime(rs.getDate("update_time"));
            article.setCreateTime(rs.getDate("create_time"));
            article.setStatus(rs.getInt("status"));
            article.setCategoryTitle(rs.getString("category_title"));
            article.setBanner(rs.getBoolean("banner"));
            articeList.add(article);
        }
        // 关闭连接
        DBUtils.closeResource(conn, st, rs);
        // 提取结果
        page.setData(articeList);
        return page;
    }

    /**
     * 推荐文章集合
     * 
     * @param limit
     * @return
     * @throws SQLException
     */
    public List<Article> findFeaturedFirst(int limit) throws SQLException {

        // 1.获取链接
        Connection conn = DBUtils.getConnection();

        // 2.拼接SQL
        String sql =
            "select a.*, b.title as category_title from article a inner join category b on a.category_id = b.id where a.status = 1 and banner=1 order by a.id desc limit ?";
        // 3.获取执行者，发送SQL
        PreparedStatement st = conn.prepareStatement(sql);
        st.setInt(1, limit);

        ResultSet rs = st.executeQuery();

        // 4.处理结果
        List<Article> articleList = new ArrayList<Article>();
        while (rs.next()) {
            // id,title,author,category_id,category_title,description,cover_img,content,view,create_time,update_time,status,banner
            Article article = new Article();
            article.setId(rs.getInt("id"));
            article.setTitle(rs.getString("title"));
            article.setAuthor(rs.getString("author"));
            article.setCategoryId(rs.getInt("category_id"));
            article.setCategoryTitle(rs.getString("category_title"));
            article.setDescription(rs.getString("description"));
            article.setCoverImg(rs.getString("cover_img"));
            article.setContent(rs.getString("content"));
            article.setView(rs.getInt("view"));
            article.setCreateTime(rs.getDate("create_time"));
            article.setUpdateTime(rs.getDate("update_time"));
            article.setStatus(rs.getInt("status"));
            article.setBanner(rs.getBoolean("banner"));
            articleList.add(article);
        }

        // 5.关闭资源
        DBUtils.closeResource(conn, st, rs);

        return articleList;
    }

    /**
     * 通过分类ID获取文章列表
     * 
     * @param categoryId
     * @return
     * @throws SQLException
     */
    public Page<Article> findByCategory(int categoryId, int pageNo) throws SQLException {

        String countSql =
            "select count(1) from article a inner join category b on a.category_id = b.id where a.category_id="
                + categoryId + " and a.status = 1";
        int totalCount = this.countAll(countSql);
        Page<Article> page = new Page<Article>(pageNo);
        page.setTotalCount(totalCount);

        // 1.获取链接
        Connection conn = DBUtils.getConnection();

        // 2.拼接SQL
        String sql =
            "select a.*,b.title as category_title from article a inner join category b on a.category_id=b.id where a.category_id=? and a.status =1 order by a.create_time limit ?,? ";
        // 3.获取执行者，发送SQL
        PreparedStatement st = conn.prepareStatement(sql);
        st.setInt(1, categoryId);
        st.setInt(2, page.getStart());
        st.setInt(3, page.getPageSize());

        ResultSet rs = st.executeQuery();

        // 4.处理结果
        List<Article> articleList = new ArrayList<Article>();
        while (rs.next()) {
            // id,title,author,category_id,category_title,description,cover_img,content,view,create_time,update_time,status,banner
            Article article = new Article();
            article.setId(rs.getInt("id"));
            article.setTitle(rs.getString("title"));
            article.setAuthor(rs.getString("author"));
            article.setCategoryId(rs.getInt("category_id"));
            article.setCategoryTitle(rs.getString("category_title"));
            article.setDescription(rs.getString("description"));
            article.setCoverImg(rs.getString("cover_img"));
            article.setContent(rs.getString("content"));
            article.setView(rs.getInt("view"));
            article.setCreateTime(rs.getDate("create_time"));
            article.setUpdateTime(rs.getDate("update_time"));
            article.setStatus(rs.getInt("status"));
            article.setBanner(rs.getBoolean("banner"));
            articleList.add(article);
        }
        page.setData(articleList);

        // 5.关闭资源
        DBUtils.closeResource(conn, st, rs);

        return page;
    }

    /**
     * 通过分类ID获取文章列表
     * 
     * @param categoryId
     * @return
     * @throws SQLException
     */
    public List<Article> findByCategory(int categoryId) throws SQLException {
        // 1.获取链接
        Connection conn = DBUtils.getConnection();

        // 2.拼接SQL
        String sql =
            "select a.*,b.title as category_title from article a inner join category b on a.category_id=b.id where a.category_id=? and a.status =1";
        // 3.获取执行者，发送SQL
        PreparedStatement st = conn.prepareStatement(sql);
        st.setInt(1, categoryId);

        ResultSet rs = st.executeQuery();

        // 4.处理结果
        List<Article> articleList = new ArrayList<Article>();
        while (rs.next()) {
            // id,title,author,category_id,category_title,description,cover_img,content,view,create_time,update_time,status,banner
            Article article = new Article();
            article.setId(rs.getInt("id"));
            article.setTitle(rs.getString("title"));
            article.setAuthor(rs.getString("author"));
            article.setCategoryId(rs.getInt("category_id"));
            article.setCategoryTitle(rs.getString("category_title"));
            article.setDescription(rs.getString("description"));
            article.setCoverImg(rs.getString("cover_img"));
            article.setContent(rs.getString("content"));
            article.setView(rs.getInt("view"));
            article.setCreateTime(rs.getDate("create_time"));
            article.setUpdateTime(rs.getDate("update_time"));
            article.setStatus(rs.getInt("status"));
            article.setBanner(rs.getBoolean("banner"));
            articleList.add(article);
        }

        // 5.关闭资源
        DBUtils.closeResource(conn, st, rs);

        return articleList;
    }

    public int modifyView(int id) throws SQLException {
        // 1.获取链接
        Connection conn = DBUtils.getConnection();
        // 2.拼接SQL
        String sql = "update article set view = view+1 where id=?";
        // 3.获取执行者，发送SQL
        PreparedStatement st = conn.prepareStatement(sql);
        st.setInt(1, id);
        int result = st.executeUpdate();
        // 5.关闭资源
        DBUtils.closeResource(conn, st);
        return result;
    }

    /** ################################################################################################### **/
    /**
     * 根据id查找到某个具体的文章信息
     * 
     * @param id
     * @return
     * @throws SQLException
     */
    public Article findById(int id) throws SQLException {
        // 1.获取链接
        Connection conn = DBUtils.getConnection();
        // 2.拼接SQL
        String sql =
            "select a.*,b.title as category_title from article a inner join category b on a.category_id=b.id where a.id=? and a.status=1";
        // 3.获取执行者，发送SQL
        PreparedStatement st = conn.prepareStatement(sql);
        st.setInt(1, id);

        ResultSet rs = st.executeQuery();

        // 4.处理结果
        Article article = null;

        while (rs.next()) {
            // id,title,author,category_id,category_title,description,cover_img,content,view,create_time,update_time,status,banner
            article = new Article();
            article.setId(rs.getInt("id"));
            article.setTitle(rs.getString("title"));
            article.setAuthor(rs.getString("author"));
            article.setCategoryId(rs.getInt("category_id"));
            article.setCategoryTitle(rs.getString("category_title"));
            article.setDescription(rs.getString("description"));
            article.setCoverImg(rs.getString("cover_img"));
            article.setContent(rs.getString("content"));
            article.setView(rs.getInt("view"));
            article.setCreateTime(rs.getDate("create_time"));
            article.setUpdateTime(rs.getDate("update_time"));
            article.setStatus(rs.getInt("status"));
            article.setBanner(rs.getBoolean("banner"));
        }
        // 5.关闭资源
        DBUtils.closeResource(conn, st, rs);
        return article;
    }

    /**
     * 逻辑删除一篇文章
     * 
     * @param id
     * @return
     * @throws SQLException
     */
    public int del(int id) throws SQLException {
        // 1.获取链接
        Connection conn = DBUtils.getConnection();

        // 2.拼接SQL
        String sql = "update article set status = 0 where id=?";
        // 3.获取执行者，发送SQL
        PreparedStatement st = conn.prepareStatement(sql);
        st.setInt(1, id);
        int result = st.executeUpdate();
        // 5.关闭资源
        DBUtils.closeResource(conn, st);
        return result;
    }

    /**
     * 保存文章
     * 
     * @param article
     * @return
     * @throws SQLException
     */
    public int add(Article article) throws SQLException {
        // insert into article(title, author,category_id, description, cover_img,
        // content,create_time, update_time) values (?,?,?,?,?,?,?,?)

        // 1.获取链接
        Connection conn = DBUtils.getConnection();
        int result = -1;
        if (article != null && article.getId() > 0) {
            String sql =
                "update article set title=?, author=?,category_id=?, description=?, "
                    + "cover_img=?, content=?, update_time=? where id=?";
            // 3.获取执行者，发送SQL
            PreparedStatement st = conn.prepareStatement(sql);
            st.setString(1, article.getTitle());
            st.setString(2, article.getAuthor());
            st.setInt(3, article.getCategoryId());
            st.setString(4, article.getDescription());
            st.setString(5, article.getCoverImg());
            st.setString(6, article.getContent());
            st.setDate(7, new java.sql.Date(new Date().getTime()));
            st.setInt(8, article.getId());
            result = st.executeUpdate();
            DBUtils.closeResource(conn, st);
        } else {
            // 2.拼接SQL
            String sql =
                "insert into article(title, author,category_id, description, cover_img, content,create_time, update_time,view,status) values (?,?,?,?,?,?,?,?,?,?)";
            // 3.获取执行者，发送SQL
            PreparedStatement st = conn.prepareStatement(sql);
            st.setString(1, article.getTitle());
            st.setString(2, article.getAuthor());
            st.setInt(3, article.getCategoryId());
            st.setString(4, article.getDescription());
            st.setString(5, article.getCoverImg());
            st.setString(6, article.getContent());
            st.setDate(7, new java.sql.Date(new Date().getTime()));
            st.setDate(8, new java.sql.Date(new Date().getTime()));
            st.setInt(9, 0);
            st.setInt(10, 1);
            result = st.executeUpdate();
            DBUtils.closeResource(conn, st);
        }
        return result;
    }

    /**
     * 查询所有
     * 
     * @param sql
     * @return
     * @throws SQLException
     */
    private int countAll(String sql) throws SQLException {
        Connection conn = DBUtils.getConnection();
        PreparedStatement st = conn.prepareStatement(sql);
        ResultSet rs = st.executeQuery();
        int totalCount = 0;
        while (rs.next()) {
            totalCount = rs.getInt(1);
        }
        DBUtils.closeResource(conn, st, rs);
        return totalCount;

    }

    /**
     * 查询所有文章的分类
     * 
     * @return
     * @throws SQLException
     */
    public List<Category> findAllCategory() throws SQLException {
        // 1.获取链接
        Connection conn = DBUtils.getConnection();
        // 2.拼接SQL
        String sql = "select * from category where type = 1 order by id asc";
        // 3.获取执行者，发送SQL
        PreparedStatement st = conn.prepareStatement(sql);
        ResultSet rs = st.executeQuery();

        // 4.处理结果
        List<Category> categoryList = new ArrayList<Category>();
        while (rs.next()) {
            // id,title,
            Category category = new Category();
            category.setId(rs.getInt("id"));
            category.setTitle(rs.getString("title"));
            categoryList.add(category);
        }
        // 5.关闭资源
        DBUtils.closeResource(conn, st, rs);
        return categoryList;
    }

    /**
     * 分页
     * 
     * @param pageNo
     * @param pageSize
     * @param categoryId
     * @param title
     * @return
     * @throws SQLException
     */
    public Page<Article> findAllByPage(int pageNo, int pageSize, int categoryId, String title) throws SQLException {
        // 1.获取链接
        Connection conn = DBUtils.getConnection();

        String countSql =
            "select count(1) from article a inner join category b on a.category_id = b.id where a.status = 1 and 1=1 ";
        if (categoryId != -1) {
            countSql += " and a.category_id=" + categoryId;
        }
        if (title != null && title.length() > 0) {
            countSql += " and a.title like '%" + title + "%'";
        }

        int totalCount = this.countAll(countSql);
        Page<Article> page = new Page<Article>(pageNo);
        page.setTotalCount(totalCount);

        // 2.拼接SQL--文件类别 跟 文件联查
        String sql =
            "select a.*, b.title as category_title from article a inner join category b on a.category_id=b.id where a.status = 1 and 1=1 ";
        if (categoryId != -1) {
            sql += " and a.category_id=" + categoryId;
        }
        if (title != null && title.length() > 0) {
            sql += " and a.title like '%" + title + "%'";
        }
        sql += " order by id desc limit ?,?";
        // 3.获取执行者，发送SQL
        PreparedStatement st = conn.prepareStatement(sql);
        st.setInt(1, (pageNo - 1) * pageSize);
        st.setInt(2, pageSize);
        ResultSet rs = st.executeQuery();

        // 4.处理结果
        List<Article> articleList = new ArrayList<Article>();
        while (rs.next()) {
            // id,title,author,category_id,category_title,description,cover_img,content,view,create_time,update_time,status,banner
            Article article = new Article();
            article.setId(rs.getInt("id"));
            article.setTitle(rs.getString("title"));
            article.setAuthor(rs.getString("author"));
            article.setCategoryId(rs.getInt("category_id"));
            article.setCategoryTitle(rs.getString("category_title"));
            article.setDescription(rs.getString("description"));
            article.setCoverImg(rs.getString("cover_img"));
            article.setContent(rs.getString("content"));
            article.setView(rs.getInt("view"));
            article.setCreateTime(rs.getDate("create_time"));
            article.setUpdateTime(rs.getDate("update_time"));
            article.setStatus(rs.getInt("status"));
            article.setBanner(rs.getBoolean("banner"));
            articleList.add(article);
        }

        // 5.关闭资源
        DBUtils.closeResource(conn, st, rs);
        page.setData(articleList);
        return page;
    }
}
